import pandas as pd
from datetime import datetime
import jsjj.excel2Mysql.pub as pub


def clear(conn, engine):
    return


def mainProcess(conn, engine):
    filelist = pub.getRawFileList(getDirectory())

    blackList = pd.read_csv('./excludeFiles')

    # 拼多多-电霸-行业总览
    for file in filelist[0]:

        if file.find('~') != -1:
            # 临时文件不处理
            continue

        # 读取
        df = get(file)

        # 转换
        dfMysql = transfer(df, file)

        # 存盘
        # 解决可能出现的超时问题bugfix 2020.08.18
        conn.connection.connection.ping(reconnect=True)
        dfMysql.to_sql(name=getTableName(), con=conn, if_exists='append', index=False)

    # 初步清洗
    clear(conn, engine)

    return


def getDirectory():
    fileDirectory = "D:\\简尚家居\\excel文件\\2020_07_08\\拼多多－源数据\\拼多多-行业-源数据\\"
    return fileDirectory


def getTableName():
    mysqlTableName = '拼多多_电霸_行业总览'
    # mysqlTableName = 'test'
    return mysqlTableName


def get(file):
    return pd.read_excel(file, sheet_name=2)


def transfer(df, fileName):
    print('本次处理文件为:' + fileName)

    columnsMysql = ['statDate', 'col1', 'col2', 'col3', 'col4', 'col5', 'col6', 'col7', 'col8', 'col9', 'col10',
                    'col11']
    dfMysql = pd.DataFrame(df)

    # 6月是不需要存储的
    # dfMysql.columns.insert(1, 'statDate')
    dfMysql.rename(
        columns={'时间': 'statDate', '一级类目': 'cat1', '二级类目': 'cat2', '三级类目': 'cat3', '叶子类目': 'catLeaf',
                 '销量': 'salesVolume', '销售额': 'salesAmount'}, inplace=True)
    # dfMysql['statDate'] = datetime.strptime(df.iat[2, 0].split('：')[1], '%Y-%m-%d %H:%M:%S')

    return dfMysql
